﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//

/* Copyright (c) 2001-2008, The HSQL Development Group
 * All rights reserved.
 *
 * Redistribution and use _in source and binary forms, with or without
 * modification, are permitted provided that the following conditions are met:
 *
 * Redistributions of source code must retain the above copyright notice, this
 * list of conditions and the following disclaimer.
 *
 * Redistributions _in binary form must reproduce the above copyright notice,
 * this list of conditions and the following disclaimer _in the documentation
 * and/or other materials provided with the distribution.
 *
 * Neither the name of the HSQL Development Group nor the names of its
 * contributors may be used to endorse or promote products derived from this
 * software without specific prior written permission.
 *
 * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
 * AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
 * IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
 * ARE DISCLAIMED. IN NO EVENT SHALL HSQL DEVELOPMENT GROUP, HSQLDB.ORG,
 * OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL,
 * EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO,
 * PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
 * LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
 * ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
 * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
 * SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
 */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
#if !SILVERLIGHT
using System.Data.Common;
using System.Data;
#endif
using EffiProz.Core.Errors;
using EffiProz.Core.Lib;
using System.Globalization;

namespace EffiProz.Core.DataTypes
{
    public sealed class DateTimeType : DTIType
    {
        public bool withTimeZone;

        public DateTimeType(int typeGroup, int type, int scale)
            : base(typeGroup, type, 0, scale)
        {



            withTimeZone = type == Types.SQL_TIME_WITH_TIME_ZONE
                           || type == Types.SQL_TIMESTAMP_WITH_TIME_ZONE;
        }

        public override int displaySize()
        {

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return 10;

                case Types.SQL_TIME:
                    return 8 + (scale == 0 ? 0
                                           : scale + 1);

                case Types.SQL_TIME_WITH_TIME_ZONE:
                    return 8 + (scale == 0 ? 0
                                           : scale + 1) + 6;

                case Types.SQL_TIMESTAMP:
                    return 19 + (scale == 0 ? 0
                                            : scale + 1);

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    return 19 + (scale == 0 ? 0
                                            : scale + 1) + 6;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override int getAdoTypeCode()
        {
            // JDBC numbers happen to be the same as SQL
#if !SILVERLIGHT
            return (int)DbType.DateTime;
#else
            return -1;
#endif
        }

        public override  String getCSharpClassName()
        {

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return "System.DateTime";

                case Types.SQL_TIME:
                case Types.SQL_TIME_WITH_TIME_ZONE:
                    return "System.DateTime";

                case Types.SQL_TIMESTAMP:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    return "System.DateTime";

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override int getAdoPrecision()
        {
            return this.displaySize();
        }

        public override int getSQLGenericTypeCode()
        {
            return Types.SQL_DATETIME;
        }

        public override String getNameString()
        {

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return Tokens.T_DATE;

                case Types.SQL_TIME:
                    return Tokens.T_TIME;

                case Types.SQL_TIME_WITH_TIME_ZONE:
                    return Tokens.T_TIME + ' ' + Tokens.T_WITH + ' '
                           + Tokens.T_TIME + ' ' + Tokens.T_ZONE;

                case Types.SQL_TIMESTAMP:
                    return Tokens.T_TIMESTAMP;

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    return Tokens.T_TIMESTAMP + ' ' + Tokens.T_WITH + ' '
                           + Tokens.T_TIME + ' ' + Tokens.T_ZONE;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override String getDefinition()
        {

            if (scale == DTIType.defaultTimeFractionPrecision)
            {
                return getNameString();
            }

            String token;

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return Tokens.T_DATE;

                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    if (scale == DTIType.defaultTimeFractionPrecision)
                    {
                        return getNameString();
                    }

                    token = Tokens.T_TIME;
                    break;

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    if (scale == DTIType.defaultTimestampFractionPrecision)
                    {
                        return getNameString();
                    }

                    token = Tokens.T_TIMESTAMP;
                    break;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }

            StringBuilder sb = new StringBuilder(16);

            sb.Append(token);
            sb.Append('(');
            sb.Append(scale);
            sb.Append(')');

            if (withTimeZone)
            {
                sb.Append(' ' + Tokens.T_WITH + ' ' + Tokens.T_TIME + ' '
                          + Tokens.T_ZONE);
            }

            return sb.ToString();
        }

        public override bool isDateTimeType()
        {
            return true;
        }

        public override bool isDateTimeTypeWithZone()
        {
            return withTimeZone;
        }

        public override bool acceptsFractionalPrecision()
        {
            return typeCode != Types.SQL_DATE;
        }

        public override SqlType getAggregateType(SqlType other)
        {

            // DATE with DATE returned here
            if (typeCode == other.typeCode)
            {
                return this.scale >= other.scale ? this
                                            : other;
            }

            if (other.typeCode == Types.SQL_ALL_TYPES)
            {
                return this;
            }

            if (other.isCharacterType())
            {
                return other.getAggregateType(this);
            }

            if (!other.isDateTimeType())
            {
                throw Error.error(ErrorCode.X_42562);
            }

            DateTimeType otherType = (DateTimeType)other;

            // DATE with TIME caught here
            if (otherType.startIntervalType > endIntervalType
                    || startIntervalType > otherType.endIntervalType)
            {
                throw Error.error(ErrorCode.X_42562);
            }

            int newType = typeCode;
            int scale = this.scale > otherType.scale ? this.scale
                                                           : otherType.scale;
            bool zone = withTimeZone || otherType.withTimeZone;
            int startType = otherType.startIntervalType > startIntervalType
                            ? startIntervalType
                            : otherType.startIntervalType;

            if (startType == Types.SQL_INTERVAL_HOUR)
            {
                newType = zone ? Types.SQL_TIME_WITH_TIME_ZONE
                               : Types.SQL_TIME;
            }
            else
            {
                newType = zone ? Types.SQL_TIMESTAMP_WITH_TIME_ZONE
                               : Types.SQL_TIMESTAMP;
            }

            return getDateTimeType(newType, scale);
        }

        public override SqlType getCombinedType(SqlType other, int operation)
        {

            switch (operation)
            {

                case OpTypes.EQUAL:
                case OpTypes.GREATER:
                case OpTypes.GREATER_EQUAL:
                case OpTypes.SMALLER_EQUAL:
                case OpTypes.SMALLER:
                case OpTypes.NOT_EQUAL:
                    {
                        if (typeCode == other.typeCode)
                        {
                            return this;
                        }

                        if (other.typeCode == Types.SQL_ALL_TYPES)
                        {
                            return this;
                        }

                        if (!other.isDateTimeType())
                        {
                            throw Error.error(ErrorCode.X_42562);
                        }

                        DateTimeType otherType = (DateTimeType)other;

                        // DATE with TIME caught here
                        if (otherType.startIntervalType > endIntervalType
                                || startIntervalType > otherType.endIntervalType)
                        {
                            throw Error.error(ErrorCode.X_42562);
                        }

                        int newType = typeCode;
                        int scale = this.scale > otherType.scale ? this.scale
                                                                       : otherType
                                                                           .scale;
                        bool zone = withTimeZone || otherType.withTimeZone;
                        int startType = otherType.startIntervalType
                                        > startIntervalType ? startIntervalType
                                                            : otherType
                                                                .startIntervalType;

                        if (startType == Types.SQL_INTERVAL_HOUR)
                        {
                            newType = zone ? Types.SQL_TIME_WITH_TIME_ZONE
                                           : Types.SQL_TIME;
                        }
                        else
                        {
                            newType = zone ? Types.SQL_TIMESTAMP_WITH_TIME_ZONE
                                           : Types.SQL_TIMESTAMP;
                        }

                        return getDateTimeType(newType, scale);
                    }
                case OpTypes.ADD:
                case OpTypes.SUBTRACT:
                    if (other.isIntervalType())
                    {
                        if (typeCode != Types.SQL_DATE && other.scale > scale)
                        {
                            return getDateTimeType(typeCode, other.scale);
                        }

                        return this;
                    }
                    break;


            }

            throw Error.error(ErrorCode.X_42562);
        }

        public override int compare(Session session, Object a, Object b)
        {

            long diff;

            if (a == b)
            {
                return 0;
            }

            if (a == null)
            {
                return -1;
            }

            if (b == null)
            {
                return 1;
            }

            switch (typeCode)
            {

                case Types.SQL_TIME:
                case Types.SQL_TIME_WITH_TIME_ZONE:
                    {
                        diff = ((TimeData)a).getSeconds()
                               - ((TimeData)b).getSeconds();

                        if (diff == 0)
                        {
                            diff = ((TimeData)a).getNanos()
                                   - ((TimeData)b).getNanos();
                        }

                        return diff == 0 ? 0
                                         : diff > 0 ? 1
                                                    : -1;
                    }
                case Types.SQL_DATE:
                case Types.SQL_TIMESTAMP:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    {
                        diff = ((TimestampData)a).getSeconds()
                               - ((TimestampData)b).getSeconds();

                        if (diff == 0)
                        {
                            diff = ((TimestampData)a).getNanos()
                                   - ((TimestampData)b).getNanos();
                        }

                        return diff == 0 ? 0
                                         : diff > 0 ? 1
                                                    : -1;
                    }
                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override Object convertToTypeLimits(SessionInterface session, Object a)
        {

            if (a == null)
            {
                return null;
            }

            if (scale == maxFractionPrecision)
            {
                return a;
            }

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return a;

                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    {
                        TimeData ti = (TimeData)a;
                        int nanos = ti.getNanos();
                        int newNanos = scaleNanos(nanos);

                        if (newNanos == nanos)
                        {
                            return ti;
                        }

                        return new TimeData(ti.getSeconds(), newNanos, ti.getZone());
                    }
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    {
                        TimestampData ts = (TimestampData)a;
                        int nanos = ts.getNanos();
                        int newNanos = scaleNanos(nanos);

                        if (newNanos == nanos)
                        {
                            return ts;
                        }

                        return new TimestampData(ts.getSeconds(), newNanos,
                                                 ts.getZone());
                    }
                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        int scaleNanos(int nanos)
        {

            int divisor = nanoScaleFactors[scale];

            return (nanos / divisor) * divisor;
        }

        public override Object convertToType(SessionInterface session, Object a,
                                    SqlType otherType)
        {

            if (a == null)
            {
                return a;
            }

            switch (otherType.typeCode)
            {

                case Types.SQL_CLOB:
                    a = a.ToString();
                    goto case Types.VARCHAR_IGNORECASE;
                //fall through
                case Types.SQL_CHAR:
                case Types.SQL_VARCHAR:
                case Types.VARCHAR_IGNORECASE:
                    switch (this.typeCode)
                    {

                        case Types.SQL_DATE:
                        case Types.SQL_TIME_WITH_TIME_ZONE:
                        case Types.SQL_TIME:
                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                        case Types.SQL_TIMESTAMP:
                            {
                                return session.getScanner().convertToDatetimeInterval(
                                    session, (String)a, this);
                            }
                    }
                    break;

                case Types.SQL_DATE:
                case Types.SQL_TIME:
                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    break;

                default:
                    throw Error.error(ErrorCode.X_42561);
            }

            switch (this.typeCode)
            {

                case Types.SQL_DATE:
                    switch (otherType.typeCode)
                    {

                        case Types.SQL_DATE:
                            return a;

                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                            {
                                long seconds = ((TimestampData)a).getSeconds()
                                               + ((TimestampData)a).getZone();
                                long l = EfzDateTime.getNormalisedDate(seconds
                                    * 1000);

                                return new TimestampData(l / 1000);
                            }
                        case Types.SQL_TIMESTAMP:
                            {
                                long l = EfzDateTime.getNormalisedDate(
                                    ((TimestampData)a).getSeconds() * 1000);

                                return new TimestampData(l / 1000);
                            }
                        default:
                            throw Error.error(ErrorCode.X_42561);
                    }
                case Types.SQL_TIME_WITH_TIME_ZONE:
                    switch (otherType.typeCode)
                    {

                        case Types.SQL_TIME_WITH_TIME_ZONE:
                            return convertToTypeLimits(session, a);

                        case Types.SQL_TIME:
                            {
                                TimeData ti = (TimeData)a;

                                return new TimeData(
                                    ti.getSeconds() - session.getZoneSeconds(),
                                    scaleNanos(ti.getNanos()),
                                    session.getZoneSeconds());
                            }
                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                            {
                                TimestampData ts = (TimestampData)a;
                                long seconds =
                                    EfzDateTime.convertToNormalisedTime(
                                        ts.getSeconds() * 1000) / 1000;

                                return new TimeData((int)(seconds),
                                                    scaleNanos(ts.getNanos()),
                                                    ts.getZone());
                            }
                        case Types.SQL_TIMESTAMP:
                            {
                                TimestampData ts = (TimestampData)a;
                                long seconds = ts.getSeconds()
                                               - session.getZoneSeconds();

                                seconds =
                                    EfzDateTime.convertToNormalisedTime(
                                        seconds * 1000) / 1000;

                                return new TimeData((int)(seconds),
                                                    scaleNanos(ts.getNanos()),
                                                    session.getZoneSeconds());
                            }
                        default:
                            throw Error.error(ErrorCode.X_42561);
                    }
                case Types.SQL_TIME:
                    switch (otherType.typeCode)
                    {

                        case Types.SQL_TIME_WITH_TIME_ZONE:
                            {
                                TimeData ti = (TimeData)a;

                                return new TimeData(ti.getSeconds() + ti.getZone(),
                                                    scaleNanos(ti.getNanos()), 0);
                            }
                        case Types.SQL_TIME:
                            return convertToTypeLimits(session, a);

                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                            {
                                TimestampData ts = (TimestampData)a;
                                long seconds = ts.getSeconds() + ts.getZone();

                                seconds =
                                    EfzDateTime.convertToNormalisedTime(
                                        seconds * 1000) / 1000;

                                return new TimeData((int)(seconds),
                                                    scaleNanos(ts.getNanos()), 0);
                            }
                        case Types.SQL_TIMESTAMP:
                            TimestampData ts1 = (TimestampData)a;
                            long seconds1 =
                                EfzDateTime.convertToNormalisedTime(
                                    ts1.getSeconds() * 1000) / 1000;

                            return new TimeData((int)(seconds1),
                                                scaleNanos(ts1.getNanos()));

                        default:
                            throw Error.error(ErrorCode.X_42561);
                    }
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    switch (otherType.typeCode)
                    {

                        case Types.SQL_TIME_WITH_TIME_ZONE:
                            {
                                TimeData ti = (TimeData)a;
                                long seconds = session.getCurrentDate().getSeconds()
                                               + ti.getSeconds();

                                return new TimestampData(seconds,
                                                         scaleNanos(ti.getNanos()),
                                                         ti.getZone());
                            }
                        case Types.SQL_TIME:
                            {
                                TimeData ti = (TimeData)a;
                                long seconds = session.getCurrentDate().getSeconds()
                                               + ti.getSeconds()
                                               - session.getZoneSeconds();

                                return new TimestampData(seconds,
                                                         scaleNanos(ti.getNanos()),
                                                         session.getZoneSeconds());
                            }
                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                            return convertToTypeLimits(session, a);

                        case Types.SQL_TIMESTAMP:
                            {
                                TimestampData ts = (TimestampData)a;
                                long seconds = ts.getSeconds()
                                               - session.getZoneSeconds();

                                return new TimestampData(seconds,
                                                         scaleNanos(ts.getNanos()),
                                                         session.getZoneSeconds());
                            }
                        case Types.SQL_DATE:
                            {
                                TimestampData ts = (TimestampData)a;

                                return new TimestampData(ts.getSeconds(), 0,
                                                         session.getZoneSeconds());
                            }
                        default:
                            throw Error.error(ErrorCode.X_42561);
                    }
                case Types.SQL_TIMESTAMP:
                    switch (otherType.typeCode)
                    {

                        case Types.SQL_TIME_WITH_TIME_ZONE:
                            {
                                TimeData ti = (TimeData)a;
                                long seconds = session.getCurrentDate().getSeconds()
                                               + ti.getSeconds()
                                               - session.getZoneSeconds();

                                return new TimestampData(seconds,
                                                         scaleNanos(ti.getNanos()),
                                                         session.getZoneSeconds());
                            }
                        case Types.SQL_TIME:
                            {
                                TimeData ti = (TimeData)a;
                                long seconds = session.getCurrentDate().getSeconds()
                                               + ti.getSeconds();

                                return new TimestampData(seconds,
                                                         scaleNanos(ti.getNanos()));
                            }
                        case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                            {
                                TimestampData ts = (TimestampData)a;
                                long seconds = ts.getSeconds() + ts.getZone();

                                return new TimestampData(seconds,
                                                         scaleNanos(ts.getNanos()));
                            }
                        case Types.SQL_TIMESTAMP:
                            return convertToTypeLimits(session, a);

                        case Types.SQL_DATE:
                            return a;

                        default:
                            throw Error.error(ErrorCode.X_42561);
                    }
                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override Object convertToDefaultType(SessionInterface session, Object a)
        {
            throw Error.error(ErrorCode.X_42561);
        }

        /** @todo - do the time zone */
        public override Object convertCSharpToSQL(SessionInterface session, Object a)
        {

            switch (typeCode)
            {

                case Types.SQL_TIME:
                case Types.SQL_TIME_WITH_TIME_ZONE:

                    if (a is DateTime)
                    {
                        long millis;
                        int nanos = 0;
                        int zoneSeconds = 0;
                        long secs = 0;

                        if (typeCode == Types.SQL_TIME)
                        {
                            millis = EfzDateTime.convertMillisFromCalendar(
                                session.getCalendar(),
                                ((DateTime)a).Ticks / TimeSpan.TicksPerMillisecond);
                        }
                        else
                        {
                            millis = ((DateTime)a).Ticks / TimeSpan.TicksPerMillisecond;
                            zoneSeconds = session.getZoneSeconds();
                        }

                        millis = EfzDateTime.getNormalisedTime(millis);
                        secs = millis / 1000;

                        nanos = (int)(((DateTime)a).Ticks * 100 - secs * 1000000000);
                        nanos = normaliseFraction(nanos, scale);


                        return new TimeData((int)secs, nanos,
                                            zoneSeconds);
                    }
                    break;

                case Types.SQL_DATE:
                    {

                        if (a is DateTime)
                        {
                            long millis;

                            millis = EfzDateTime.convertMillisFromCalendar(
                                session.getCalendar(), ((DateTime)a).Ticks / TimeSpan.TicksPerMillisecond);
                            millis = EfzDateTime.getNormalisedDate(millis);

                            return new TimestampData(millis / 1000);
                        }

                        break;
                    }
                case Types.SQL_TIMESTAMP:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    {


                        if (a is DateTime)
                        {
                            long millis;
                            int nanos = 0;
                            int zoneSeconds = 0;
                            //long secs = 0;

                            if (typeCode == Types.SQL_TIME)
                            {
                                millis = EfzDateTime.convertMillisFromCalendar(
                                    session.getCalendar(),
                                    ((DateTime)a).Ticks / TimeSpan.TicksPerMillisecond);
                            }
                            else
                            {
                                millis = ((DateTime)a).Ticks / TimeSpan.TicksPerMillisecond;
                                zoneSeconds = session.getZoneSeconds();
                            }



                            nanos = (int)(((DateTime)a).Ticks * 100 - millis * TimeSpan.TicksPerMillisecond*100);
                            nanos = normaliseFraction(nanos, scale);


                            return new TimestampData(millis/1000, nanos,
                                                zoneSeconds);
                        }

                        break;
                    }
            }

            throw Error.error(ErrorCode.X_42561);
        }

        /*
                    return (Time) Type.SQL_TIME.getJavaDateObject(session, t, 0);
            java.sql.Timestamp value = new java.sql.Timestamp(
                (((TimestampData) o).getSeconds() - ((TimestampData) o)
                    .getZone()) * 1000);
        */
        public Object convertSQLToCSharpGMT(SessionInterface session, Object a)
        {

            long millis;

            switch (typeCode)
            {

                case Types.SQL_TIME:
                case Types.SQL_TIME_WITH_TIME_ZONE:
                    millis = ((TimeData)a).getSeconds() * 1000;

                    return new DateTime(millis * TimeSpan.TicksPerMillisecond);

                case Types.SQL_DATE:
                    millis = ((TimestampData)a).getSeconds() * 1000;

                    return new DateTime(millis * TimeSpan.TicksPerMillisecond);

                case Types.SQL_TIMESTAMP:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:

                    DateTime value = new DateTime(((TimestampData)a).getSeconds() * 100000000 + ((TimestampData)a).getNanos() / 100);

                    return value;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override Object convertSQLToCSharp(SessionInterface session, Object a)
        {

            switch (typeCode)
            {

                case Types.SQL_TIME:
                    {
                        System.Globalization.Calendar cal = session.getCalendar();
                        long millis = EfzDateTime.convertMillisToCalendar(cal,
                            ((TimeData)a).getSeconds() * 1000);

                        millis = EfzDateTime.getNormalisedTime(cal, millis);

                        DateTime value = new DateTime(millis * TimeSpan.TicksPerMillisecond);

                        return value;
                    }
                case Types.SQL_TIME_WITH_TIME_ZONE:
                    {
                        int seconds = ((TimeData)a).getSeconds();

                        return new DateTime(seconds *  TimeSpan.TicksPerSecond);
                    }
                case Types.SQL_DATE:
                    {
                        System.Globalization.Calendar cal = session.getCalendar();
                        long millis = EfzDateTime.convertMillisToCalendar(cal,
                            ((TimestampData)a).getSeconds() * 1000);

                        // millis = HsqlDateTime.getNormalisedDate(cal, millis);
                        DateTime value = new DateTime(millis * TimeSpan.TicksPerMillisecond);

                        return value;
                    }
                case Types.SQL_TIMESTAMP:
                    {
                        System.Globalization.Calendar cal = session.getCalendar();
                        long millis = EfzDateTime.convertMillisToCalendar(cal,
                            ((TimestampData)a).getSeconds() * 1000);
                        DateTime value = new DateTime(millis * TimeSpan.TicksPerMillisecond
                            + ((TimestampData)a).getNanos() / 100);

                        return value;
                    }
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    {
                        long seconds = ((TimestampData)a).getSeconds();
                        DateTime value = new DateTime(seconds * TimeSpan.TicksPerSecond
                          + ((TimestampData)a).getNanos() / 100);

                        return value;
                    }
                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public static int normaliseTime(int seconds)
        {

            while (seconds < 0)
            {
                seconds += 24 * 60 * 60;
            }

            if (seconds > 24 * 60 * 60)
            {
                seconds %= 24 * 60 * 60;
            }

            return seconds;
        }

        public override String convertToString(Object a)
        {

            //bool zone = false;
            String s;
            StringBuilder sb;

            if (a == null)
            {
                return null;
            }

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    return EfzDateTime.getDateString(
                        ((TimestampData)a).getSeconds());

                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    {
                        TimeData t = (TimeData)a;
                        int seconds = normaliseTime(t.getSeconds() + t.getZone());

                        s = intervalSecondToString(seconds, t.getNanos(), false);

                        if (!withTimeZone)
                        {
                            return s;
                        }

                        sb = new StringBuilder(s);
                        s = SqlType.SQL_INTERVAL_HOUR_TO_MINUTE.intervalSecondToString(
                            ((TimeData)a).getZone(), 0, true);

                        sb.Append(s);

                        return sb.ToString();
                    }
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    {
                        TimestampData ts = (TimestampData)a;

                        sb = new StringBuilder();

                        EfzDateTime.getTimestampString(sb,
                                                        ts.getSeconds()
                                                        + ts.getZone(), ts.getNanos(),
                                                            scale);

                        if (!withTimeZone)
                        {
                            return sb.ToString();
                        }

                        s = SqlType.SQL_INTERVAL_HOUR_TO_MINUTE.intervalSecondToString(
                            ((TimestampData)a).getZone(), 0, true);

                        sb.Append(s);

                        return sb.ToString();
                    }
                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public override String convertToSQLString(Object a)
        {

            if (a == null)
            {
                return Tokens.T_NULL;
            }

            StringBuilder sb = new StringBuilder(32);

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    sb.Append(Tokens.T_DATE);
                    break;

                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    sb.Append(Tokens.T_TIME);
                    break;

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    sb.Append(Tokens.T_TIMESTAMP);
                    break;
            }

            sb.Append(StringConverter.toQuotedString(convertToString(a), '\'',
                    false));

            return sb.ToString();
        }

        public override bool canConvertFrom(SqlType otherType)
        {

            if (otherType.typeCode == Types.SQL_ALL_TYPES)
            {
                return true;
            }

            if (otherType.isCharacterType())
            {
                return true;
            }

            if (!otherType.isDateTimeType())
            {
                return false;
            }

            if (otherType.typeCode == Types.SQL_DATE)
            {
                return typeCode != Types.SQL_TIME;
            }
            else if (otherType.typeCode == Types.SQL_TIME)
            {
                return typeCode != Types.SQL_DATE;
            }

            return true;
        }

        public override Object add(Object a, Object b, SqlType otherType)
        {

            if (a == null || b == null)
            {
                return null;
            }

            switch (typeCode)
            {

                /** @todo -  range checks for units added */
                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    if (b is IntervalMonthData)
                    {
                        throw Error.runtimeError(ErrorCode.U_S0500,
                                                 "DateTimeType");
                    }
                    else if (b is IntervalSecondData)
                    {
                        return addSeconds((TimeData)a,
                                          (int)((IntervalSecondData)b).units,
                                          ((IntervalSecondData)b).nanos);
                    }
                    break;

                case Types.SQL_DATE:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    if (b is IntervalMonthData)
                    {
                        return addMonths((TimestampData)a,
                                         (int)((IntervalMonthData)b).units);
                    }
                    else if (b is IntervalSecondData)
                    {
                        return addSeconds((TimestampData)a,
                                          (int)((IntervalSecondData)b).units,
                                          ((IntervalSecondData)b).nanos);
                    }
                    break;

            }

            throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
        }

        public override Object subtract(Object a, Object b, SqlType otherType)
        {

            if (a == null || b == null)
            {
                return null;
            }

            switch (typeCode)
            {

                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                    if (b is IntervalMonthData)
                    {
                        throw Error.runtimeError(ErrorCode.U_S0500,
                                                 "DateTimeType");
                    }
                    else if (b is IntervalSecondData)
                    {
                        return addSeconds((TimeData)a,
                                          -(int)((IntervalSecondData)b).units,
                                          -((IntervalSecondData)b).nanos);
                    }
                    break;

                case Types.SQL_DATE:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP:
                    if (b is IntervalMonthData)
                    {
                        return addMonths((TimestampData)a,
                                         -(int)((IntervalMonthData)b).units);
                    }
                    else if (b is IntervalSecondData)
                    {
                        return addSeconds((TimestampData)a,
                                          -(int)((IntervalSecondData)b).units,
                                          -((IntervalSecondData)b).nanos);
                    }
                    break;

            }

            throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
        }

        public override bool Equals(Object other)
        {
            if (other is SqlType)
            {
                return base.Equals(other)
                       && ((DateTimeType)other).withTimeZone == withTimeZone;
            }

            return false;
        }

        public override int GetHashCode()
        {
            return base.GetHashCode() ^ withTimeZone.GetHashCode();
        }

        public enum CalendarPart
        {
            YEAR, MONTH, DAY_OF_MONTH, HOUR_OF_DAY, MINUTE, SECOND, DAY_OF_WEEK, DAY_OF_YEAR, WEEK_OF_YEAR
        }

        public override int getPart(Session session, Object dateTime, int part)
        {

            int calendarPart;
            long ticks;

            if (typeCode == Types.SQL_TIME
                    || typeCode == Types.SQL_TIME_WITH_TIME_ZONE)
            {
                ticks =
                    (((TimeData)dateTime).getSeconds() + ((TimeData)dateTime)
                        .getZone()) * TimeSpan.TicksPerSecond;
            }
            else
            {
                ticks =
                    (((TimestampData)dateTime)
                        .getSeconds() + ((TimestampData)dateTime).getZone()) * TimeSpan.TicksPerSecond;
            }

            DateTime datetime = new DateTime(ticks);         

            switch (part)
            {
                case Types.SQL_INTERVAL_YEAR:
                    calendarPart = datetime.Year;              
                    break;

                case Types.SQL_INTERVAL_MONTH:
                    calendarPart = datetime.Month;
                    break;

                case Types.SQL_INTERVAL_DAY:
                case DAY_OF_MONTH:
                    calendarPart = datetime.Day;
                    break;

                case Types.SQL_INTERVAL_HOUR:
                    calendarPart = datetime.Hour;
                    break;

                case Types.SQL_INTERVAL_MINUTE:
                    calendarPart = datetime.Minute;
                    break;

                case Types.SQL_INTERVAL_SECOND:
                    calendarPart = datetime.Second;
                    break;

                case DAY_OF_WEEK:
                    calendarPart = ((int)datetime.DayOfWeek) +1;
                    break;

                case WEEK_OF_YEAR:
                     System.Globalization.CultureInfo ciCurr = System.Globalization.CultureInfo.CurrentCulture;
                     calendarPart = ciCurr.Calendar.GetWeekOfYear(datetime,
                         System.Globalization.CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);
                    break;

                case SECONDS_MIDNIGHT:
                    {
                        if (typeCode == Types.SQL_TIME
                                || typeCode == Types.SQL_TIME_WITH_TIME_ZONE) { }
                        else
                        {
                            try
                            {
                                SqlType target = withTimeZone
                                              ? SqlType.SQL_TIME_WITH_TIME_ZONE
                                              : SqlType.SQL_TIME;

                                dateTime = target.castToType(session, dateTime, this);
                            }
                            catch (CoreException) { }
                        }

                        calendarPart = ((TimeData)dateTime).getSeconds();
                    }
                    break;
                case TIMEZONE_HOUR:
                    if (typeCode == Types.SQL_TIMESTAMP_WITH_TIME_ZONE)
                    {
                        calendarPart = ((TimestampData)dateTime).getZone() / 3600;
                    }
                    else
                    {
                        calendarPart = ((TimeData)dateTime).getZone() / 3600;
                    }
                    break;
                case TIMEZONE_MINUTE:
                    if (typeCode == Types.SQL_TIMESTAMP_WITH_TIME_ZONE)
                    {
                        calendarPart = ((TimestampData)dateTime).getZone() / 60 % 60;
                    }
                    else
                    {
                        calendarPart = ((TimeData)dateTime).getZone() / 60 % 60;
                    }
                    break;
                case QUARTER:                 
                    calendarPart = ((datetime.Month -1)/3) + 1;
                    break;

                case DAY_OF_YEAR:
                    calendarPart = datetime.DayOfYear;
                    break;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500,
                                             "DateTimeType - " + part);
            }

            return calendarPart;
        }



        public override Decimal getSecondPart(Object dateTime)
        {

            long seconds = getPart(null, dateTime, Types.SQL_INTERVAL_SECOND);
            int nanos = 0;

            if (typeCode == Types.SQL_TIMESTAMP)
            {
                nanos = ((TimestampData)dateTime).getNanos();
            }
            else if (typeCode == Types.SQL_TIME)
            {
                nanos = ((TimeData)dateTime).getNanos();
            }

            return getSecondPart(seconds, nanos);
        }

        public String getPartString(Session session, Object dateTime, int part)
        {

            String pattern = "";

            switch (part)
            {

                case DAY_NAME:
                    pattern = "EEEE";
                    break;

                case MONTH_NAME:
                    pattern = "MMMM";
                    break;
            }

            //SimpleDateFormat format = session.getSimpleDateFormatGMT();

            //try {
            //    format.applyPattern(javaPattern);
            //} catch (Exception e) {}

            DateTime date = (DateTime)convertSQLToCSharpGMT(session, dateTime);

            return date.ToString(pattern);
        }

        public Object getValue(long seconds, int nanos, int zoneSeconds)
        {

            switch (typeCode)
            {

                case Types.SQL_DATE:
                    seconds =
                        EfzDateTime.getNormalisedDate(
                            (seconds + zoneSeconds) * 1000) / 1000;

                    return new TimestampData(seconds);

                case Types.SQL_TIME_WITH_TIME_ZONE:
                    seconds = EfzDateTime.getNormalisedDate(seconds * 1000)
                              / 1000;

                    return new TimeData((int)seconds, nanos, zoneSeconds);

                case Types.SQL_TIME:
                    seconds =
                        EfzDateTime.getNormalisedTime(
                            (seconds + zoneSeconds) * 1000) / 1000;

                    return new TimeData((int)seconds, nanos);

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    return new TimestampData(seconds, nanos, zoneSeconds);

                case Types.SQL_TIMESTAMP:
                    return new TimestampData(seconds + zoneSeconds, nanos);

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public static DateTimeType getDateTimeType(int type, int scale)
        {

            if (scale > DTIType.maxFractionPrecision)
            {
                throw Error.error(ErrorCode.X_42592);
            }

            switch (type)
            {

                case Types.SQL_DATE:
                    return SQL_DATE;

                case Types.SQL_TIME:
                    if (scale != DTIType.defaultTimeFractionPrecision)
                    {
                        return new DateTimeType(Types.SQL_TIME, type, scale);
                    }

                    return SQL_TIME;

                case Types.SQL_TIME_WITH_TIME_ZONE:
                    if (scale != DTIType.defaultTimeFractionPrecision)
                    {
                        return new DateTimeType(Types.SQL_TIME, type, scale);
                    }

                    return SQL_TIME_WITH_TIME_ZONE;

                case Types.SQL_TIMESTAMP:
                    if (scale != DTIType.defaultTimestampFractionPrecision)
                    {
                        return new DateTimeType(Types.SQL_TIMESTAMP, type, scale);
                    }

                    return SQL_TIMESTAMP;

                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    if (scale != DTIType.defaultTimestampFractionPrecision)
                    {
                        return new DateTimeType(Types.SQL_TIMESTAMP, type, scale);
                    }

                    return SQL_TIMESTAMP_WITH_TIME_ZONE;

                default:
                    throw Error.runtimeError(ErrorCode.U_S0500, "DateTimeType");
            }
        }

        public Object changeZone(Object a, SqlType otherType, int targetZone,
                                 int localZone)
        {

            if (a == null)
            {
                return null;
            }

            if (otherType.typeCode == Types.SQL_TIMESTAMP_WITH_TIME_ZONE
                    || otherType.typeCode == Types.SQL_TIME_WITH_TIME_ZONE)
            {
                localZone = 0;
            }

            if (targetZone > DTIType.timezoneSecondsLimit
                    || -targetZone > DTIType.timezoneSecondsLimit)
            {
                throw Error.error(ErrorCode.X_22009);
            }

            switch (typeCode)
            {

                case Types.SQL_TIME_WITH_TIME_ZONE:
                    {
                        TimeData value = (TimeData)a;

                        if (localZone != 0 || value.getZone() != targetZone)
                        {
                            return new TimeData(value.getSeconds() - localZone,
                                                value.getNanos(), targetZone);
                        }

                        break;
                    }
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                    {
                        TimestampData value = (TimestampData)a;

                        if (localZone != 0 || value.getZone() != targetZone)
                        {
                            return new TimestampData(value.getSeconds() - localZone,
                                                     value.getNanos(), targetZone);
                        }

                        break;
                    }
            }

            return a;
        }

        public bool canAdd(IntervalType other)
        {
            return other.startPartIndex >= startPartIndex
                   && other.endPartIndex <= endPartIndex;
        }

        public static bool? overlaps(Session session, Object[] a, SqlType[] ta,
                                       Object[] b, SqlType[] tb)
        {

            if (a == null || b == null)
            {
                return null;
            }

            if (a[0] == null || b[0] == null)
            {
                return null;
            }

            if (a[1] == null)
            {
                a[1] = a[0];
            }

            if (b[1] == null)
            {
                b[1] = b[0];
            }

            SqlType commonType = ta[0].getCombinedType(tb[0], OpTypes.EQUAL);

            a[0] = commonType.castToType(session, a[0], ta[0]);
            b[0] = commonType.castToType(session, b[0], tb[0]);

            if (ta[1].isIntervalType())
            {
                a[1] = commonType.add(a[0], a[1], ta[1]);
            }
            else
            {
                a[1] = commonType.castToType(session, a[1], ta[1]);
            }

            if (tb[1].isIntervalType())
            {
                b[1] = commonType.add(b[0], b[1], tb[1]);
            }
            else
            {
                b[1] = commonType.castToType(session, b[1], tb[1]);
            }

            if (commonType.compare(session, a[0], a[1]) > 0)
            {
                Object temp = a[0];

                a[0] = a[1];
                a[1] = temp;
            }

            if (commonType.compare(session, b[0], b[1]) > 0)
            {
                Object temp = b[0];

                b[0] = b[1];
                b[1] = temp;
            }

            if (commonType.compare(session, a[0], b[0]) > 0)
            {
                Object[] temp = a;

                a = b;
                b = temp;
            }

            if (commonType.compare(session, a[1], b[0]) > 0)
            {
                return true;
            }

            return false;
        }

        //
        public static int subtractMonths(TimestampData a, TimestampData b,
                                         bool isYear)
        {
            lock (EfzDateTime.tempCalGMT)
            {
                
                bool negate = false;

                if (b.getSeconds() > a.getSeconds())
                {
                    negate = true;

                    TimestampData temp = a;

                    a = b;
                    b = temp;
                }

                DateTime tmpb = new DateTime(b.getSeconds() * TimeSpan.TicksPerSecond, DateTimeKind.Utc);

                int months = EfzDateTime.tempCalGMT.GetMonth(tmpb);
                int years = EfzDateTime.tempCalGMT.GetYear(tmpb);

                DateTime tmpa = new DateTime(a.getSeconds() * TimeSpan.TicksPerSecond, DateTimeKind.Utc);



                tmpa = EfzDateTime.tempCalGMT.AddMonths(tmpa, -1 * months);
                tmpa = EfzDateTime.tempCalGMT.AddYears(tmpa, -1 * years);

                months = tmpa.Month;
                years = tmpa.Year;

                if (isYear)
                {
                    months = years * 12;
                }
                else
                {
                    if (months < 0)
                    {
                        months += 12;

                        years--;
                    }

                    months += years * 12;

                    if (negate)
                    {
                        months = -months;
                    }
                }

                return months;
            }
            
        }

        /** @todo - overflow */
        public static TimeData addSeconds(TimeData source, int seconds,
                                          int nanos)
        {

            nanos += source.getNanos();
            seconds += nanos / limitNanoseconds;
            nanos %= limitNanoseconds;

            if (nanos < 0)
            {
                nanos += DTIType.limitNanoseconds;

                seconds--;
            }

            seconds += source.getSeconds();
            seconds %= (24 * 60 * 60);

            TimeData ti = new TimeData(seconds, nanos, source.getZone());

            return ti;
        }

        /** @todo - overflow */
        public static TimestampData addMonths(TimestampData source, int months)
        {

            int n = source.getNanos();

            lock (EfzDateTime.tempCalGMT)
            {
                DateTime tmp = new DateTime(source.getSeconds() * TimeSpan.TicksPerSecond, DateTimeKind.Utc);

                tmp = EfzDateTime.tempCalGMT.AddMonths(tmp, months);

                TimestampData ts =
                    new TimestampData(tmp.Ticks / TimeSpan.TicksPerSecond
                                      , n, source.getZone());

                return ts;
            }
        }

        /** @todo - overflow */
        public static TimestampData addSeconds(TimestampData source, int seconds,
                                               int nanos)
        {

            nanos += source.getNanos();
            seconds += nanos / limitNanoseconds;
            nanos %= limitNanoseconds;

            if (nanos < 0)
            {
                nanos += limitNanoseconds;

                seconds--;
            }

            long newSeconds = source.getSeconds() + seconds;
            TimestampData ts = new TimestampData(newSeconds, nanos,
                                                 source.getZone());

            return ts;
        }
    }
}
